
package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 * @version 12/22/2011
 * @author Gruppe 4
 */
public class DBProduct implements IFDBProduct
{
    private  Connection con;
    PreparedStatement pStmtSelect;
    
    /** Creates a new instance of DBProduct */
    public DBProduct() {
      con = DBConnection.getInstance().getDBcon();
    }
    
    /**
     * Method that finds all product descriptions
     * As of yet not implemented - not in use
     */ 
    @Override
    public ArrayList<ProductDescription> getAllProductDescriptions(boolean retriveAssociation)
    {
        return null;
    }
    
    /**
     * The method finds a product description with a specified id
     * @param proDescId Id of the product description
     * @param retrieveAssociation True: build associations; False: Skip building associations
     * @return ProductDescription The product description that has been found
     */
    @Override
    public ProductDescription findProductDescription(int proDescId, boolean retrieveAssociation)
    {
        String wClause = "  where proDescId = ";
        ProductDescription proObj = new ProductDescription();
     
        proObj = findWithPreQueryId(proDescId, wClause, retrieveAssociation);
        return proObj;
    }
    
    /**
     * The method finds the product descriptions matching a specified product type
     * @param proType Identification of the product type
     * @param retrieveAssociation True: build associations; False: Skip building associations
     * @return ArrayList<ProductDescription> ArrayList of product descriptions
     */
    @Override
    public ArrayList<ProductDescription> findProductDescriptionPType(String proType, boolean retrieveAssociation)
    {
        String wClause = "  where productType = ";
       
        return findAllWithPreQuery(proType, wClause, retrieveAssociation);
        
    }
    
    /**
     * Not implemented yet
     * @param name
     * @param retriveAssociation True: build associations; False: Skip building associations
     * @return 
     */
    @Override
     public ProductDescription SearchProductName(String name, boolean retriveAssociation)
    {   String wClause = "  where name = " + name + "";
        return null;
    }
    
    @Override
    public int deleteProduct(int productId)
	{
//               int rc=-1;
//	  
//	  	String query="DELETE FROM Product WHERE productId = '" +
//				productId + "'";
//                System.out.println(query);
//	  	try{ // delete from product
//	 		Statement stmt = con.createStatement();
//	 		stmt.setQueryTimeout(5);
//	 	  	rc = stmt.executeUpdate(query);
//	 	  	stmt.close();
//  		}//slut try	
//   	        catch(Exception ex){
//	 	  	System.out.println("Delete exception in Product db: "+ex);
//   	        }
		return 0; //(rc);
	}
       
    @Override
    public int updateProduct(Product pro)
    {
//            int rc=-1;
//            String query = "";
//                      
//            query="UPDATE Product SET "+  
//		 	  "name ='"+ pro.getName()+"' " + 
//		          " WHERE productId = '"+ pro.getProductId() + "'";
//            
//            System.out.println("Update query:" + query);
//                
//                try{ // update product
//	 		Statement stmt = con.createStatement();
//	 		stmt.setQueryTimeout(5);
//	 	 	rc = stmt.executeUpdate(query);
//
//	 	 	stmt.close();
//		}//slut try
//	 	catch(Exception ex){
//	 	 	System.out.println("Update exception in product db: "+ex);
//	  	}
//		return(rc);
            return 0;
	}

    //ved oprettelse af en varebeskrivelse skal der evt. oprettes et produkt også??
    //eller disse tilføjes blot senere
    
    /**
     * The method inserts a new product description and a product
     * @param proDesc An object of type product description
     * @param pro An object of type Product
     * @return int int an int 1 if the method succedes and -1 if the method fails
     * @throws Exception 
     */
    @Override
    public int insertProduct(ProductDescription proDesc, Product pro) throws Exception
    { 
       //call to get the next product description id
       int nextProDescId = GetMax.getMaxId("Select max(proDescId) from ProductDescription");
       nextProDescId = nextProDescId + 1;
       System.out.println("next product description id = " +  nextProDescId);
        
       int rc = -1;
       String query = "";
       
       PreparedStatement pstmt = null;
       PreparedStatement pstmt2 = null; // evt em pstmt mere for leverandør
       String insertProductDescription = "insert into ProductDescription(proDescId, name, purchasePrice, salesPrice, description, favorite, supplierId, productType)"+
                     "values (?,?,?,?,?,?,?,?)";
       
       System.out.println(insertProductDescription);
            
       pstmt = con.prepareStatement(insertProductDescription);
       //pstmt2 = getSqlInsert(pro, nextProDescId);
       System.out.println(pstmt2);
       pstmt.setInt(1, nextProDescId);
       pstmt.setString(2, proDesc.getName());
       pstmt.setDouble(3, proDesc.getPurchasePrice());
       pstmt.setDouble(4, proDesc.getSalesPrice());
       pstmt.setString(5, proDesc.getDescription());
       pstmt.setBoolean(6, proDesc.isFavorite());
       pstmt.setInt(7, proDesc.getSupplier().getSupplierId());
       pstmt.setString(8, proDesc.getProductType());
           
       System.out.println(pstmt2);
       
       rc = pstmt.executeUpdate();   
       //rc = pstmt2.executeUpdate();
                     
       return rc;
    }
    
    /**
     * The method sets a string to be used in a select statement
     * @param pro An object of type Product
     * @return String SQL select string
     */
    private String getSqlSelect(Product pro)
    {
        String sqlSelect = "";
        
        if(pro instanceof Pipe)
        {
            sqlSelect = "Select max(productId) from Pipe";
        }
        if(pro instanceof Fitting)
        {
            sqlSelect = "Select max(productId) from Fitting";
        }
        if(pro instanceof BackFill)
        {
            sqlSelect = "Select max(productId) from BackFill";
        }
        if(pro instanceof SepticSystem)
        {
            sqlSelect = "Select max(productId) from SepticSystem";
        }
        if(pro instanceof Misc)
        {
            sqlSelect = "Select max(productId) from Misc";
        }
        
        return sqlSelect;
    }
    
    private PreparedStatement getSqlInsert(Product pro, int proDescId) throws Exception
    {
        
       //call to get the next productId
       int nextProductId = GetMax.getMaxId(getSqlSelect(pro));
       nextProductId = nextProductId + 1;
       System.out.println("next product id = " +  nextProductId);
        
       String insertProduct = "";
       PreparedStatement pstmt2 = null;
        
       if(pro instanceof Pipe)
       {
            insertProduct = "insert into Pipe(productId, proDescId, diameter, length)"+
                     "values (?,?,?,?)";
            
            System.out.println(insertProduct);
            pstmt2 = con.prepareStatement(insertProduct);
            
            Pipe pipe = (Pipe) pro;
            pstmt2.setInt(1, nextProductId);
            pstmt2.setInt(2, proDescId);
            pstmt2.setDouble(3, pipe.getDiameter());
            pstmt2.setDouble(4, pipe.getLength());
       }
       if(pro instanceof Fitting)
       {
            insertProduct = "insert into Fitting(productId, proDescId, size, type)"+
                     "values (?,?,?,?)";
            
            Fitting fitting = (Fitting) pro;
            pstmt2.setInt(1, nextProductId);
            pstmt2.setInt(2, proDescId);
            pstmt2.setString(3, fitting.getSize());
            pstmt2.setString(4, fitting.getType());
       }
       if(pro instanceof BackFill)
       {
            insertProduct = "insert into BackFill(productId, material)"+
                     "values (?,?)";
            
            BackFill backFill = (BackFill) pro;
            pstmt2.setInt(1, backFill.getProductId());
            pstmt2.setString(2, backFill.getMaterial());
       }
       if(pro instanceof SepticSystem)
       {
            insertProduct = "insert into SepticSystem(productId, type, volume)"+
                     "values (?,?,?)";
            
            SepticSystem sepSys = (SepticSystem) pro;
            pstmt2.setInt(1, sepSys.getProductId());
            pstmt2.setString(2, sepSys.getType());
            pstmt2.setDouble(3, sepSys.getVolume());
       }
       if(pro instanceof Misc)
       {
            insertProduct = "insert into Misc(productId, type, size)"+
                     "values (?,?,?)";
            
            Misc misc = (Misc) pro;
            pstmt2.setInt(1, misc.getProductId());
            pstmt2.setString(2, misc.getType());
            pstmt2.setString(3, misc.getSize());
       }   
       return pstmt2;
    }
     
    /**
     * 
     * @param value
     * @param wClause
     * @param retrieveAssociation True: build associations; False: Skip building associations
     * @return 
     */
    private ProductDescription findWithPreQueryId(Object value, String wClause, boolean retrieveAssociation)
    {   System.out.println("Find with preCompiled");
            
            int proId = 0;
            String proType = "";
            
            if(pStmtSelect == null)
            {
               pStmtSelect =  getPreparedStatement2(wClause);   
            }
            ProductDescription proObj = null;
            ResultSet results;
            ArrayList<Product> products = null;
            try
            {   
                if(value instanceof Integer)
                {
                    proId = (Integer) value;
                    pStmtSelect.setInt(1,proId);
                }
                if(value instanceof String)
                {
                    proType = (String) value;
                    pStmtSelect.setString(1,proType);
                }
           
            results = pStmtSelect.executeQuery();
                if(results.next())
                {
                    proObj = buildProduct2(results);
                    
                    //not implemented yet
                    if(retrieveAssociation)
                    {
//                        products = findProducts(proDescId, productType);
//                        proObj.setProductArray(products);
//                    
//                        System.out.println("Products are selected");
                    }
                }
            }
            catch(Exception e)
            {
                System.out.println(e.getMessage());
            }
            pStmtSelect = null;
            return proObj;
       }

   /**
     * 
     * @param value
     * @param wClause
     * @param retrieveAssociation True: build associations; False: Skip building associations
     * @return 
     */
   private ArrayList<ProductDescription> findAllWithPreQuery(Object value, String wClause, boolean retrieveAssociation)
   {   System.out.println("Find with preCompiled");
            
            int proId = 0;
            String proType = "";
            
            if(pStmtSelect == null)
            {
               pStmtSelect =  getPreparedStatement2(wClause);   
            }
            ArrayList<ProductDescription> list = new ArrayList<ProductDescription>();
            ProductDescription proObj = null;
            ResultSet results;
            try
            {   
                if(value instanceof Integer)
                {
                    proId = (Integer) value;
                    pStmtSelect.setInt(1,proId);
                }
                if(value instanceof String)
                {
                    proType = (String) value;
                    pStmtSelect.setString(1,proType);
                }
           
            results = pStmtSelect.executeQuery();
               
                while( results.next() )
            { 
                    proObj = buildProduct2(results);	
                    list.add(proObj);  
            }//end while
                   
            }
            catch(Exception e)
            {
                System.out.println(e.getMessage());
            }
            pStmtSelect = null;
            return list;
       }

     /**
     * 
     * @param value
     * @param wClause
     * @param retrieveAssociation True: build associations; False: Skip building associations
     * @return 
     */
    private ArrayList<Product> findPruductsWithPreQuery(Object value, String wClause, boolean retrieveAssociation, String productType)
    {
        System.out.println("Find with preCompiled");
        int proDescId = 0;
        String orderDate = "";
            
        if(pStmtSelect == null)
        {
            pStmtSelect =  getPreparedStatement2(wClause);          
        }
        ArrayList<Product> list = new ArrayList<Product>();
        ResultSet results;
        try
        {
            if(value instanceof Integer)
            {
                proDescId = (Integer) value;
                pStmtSelect.setInt(1,proDescId);
            }
            if(value instanceof String)
            {
                productType = (String) value;
                pStmtSelect.setString(1,orderDate);
            }
           
            results = pStmtSelect.executeQuery();
            while( results.next() )
            {
                if(productType.equalsIgnoreCase("3"))
                {
                    Product proObj = new Pipe();
                    proObj = buildProduct(results, productType);	
                    list.add(proObj);
                }
            }//end while
            
            
            
        } 
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        
        return list;
    }
   
    private PreparedStatement getPreparedStatement2(String wClause)
    {
        try
        {
            String query = "SELECT * FROM ProductDescription" +
                    wClause + " ?";
            
            System.out.println("Preparestatement " + query);
            pStmtSelect = con.prepareStatement(query);
        }//end try
        catch(Exception e)
        {
            e.getMessage();
        }
        return pStmtSelect;
    }
    
    /**
     * The method builds an object of type ProductDescription
     * @param results
     * @param retrieveAssociation True: build associations; False: Skip building associations
     * @return ProductDescription The product description that has been build
     */
    private ProductDescription buildProduct2(ResultSet results)
    {   
        ProductDescription proObj = new ProductDescription();  
        ArrayList<Product> products = null;
          
        try
        {
            int proDescId = results.getInt("proDescId");
            String name = results.getString("name");
            double purchasePrice = results.getDouble("purchasePrice");
            double salesPrice = results.getDouble("salesPrice");
            String description = results.getString("description");
            boolean favorite = results.getBoolean("favorite");
            String productType = results.getString("productType");
	  	
            proObj = new ProductDescription(proDescId, name, purchasePrice, salesPrice, description, favorite);      
        }
        catch(Exception e)
        {
            System.out.println("error in building the ProductDescription object");
            System.out.println(e.getMessage());
        }
        return proObj;
   }
    
    /**
     * The method builds an object of type Product
     * @param results
     * @param productType
     * @return Product An object of type Product
     */
    private Product buildProduct(ResultSet results, String productType)
    {   
        Product proObj = null;
        
        if(productType.equalsIgnoreCase("1"))
        {
            proObj = new Misc(); 
        }
        if(productType.equalsIgnoreCase("2"))
        {
            proObj = new BackFill(); 
        }
        if(productType.equalsIgnoreCase("3"))
        {
            proObj = new Pipe(); 
        }
        if(productType.equalsIgnoreCase("4"))
        {
            proObj = new Fitting(); 
        }
        if(productType.equalsIgnoreCase("5"))
        {
            proObj = new SepticSystem(); 
        }
         
        ArrayList<Product> products = null;
          
        try
        {
            if(proObj instanceof Pipe)
            {
                Pipe pipe = (Pipe) proObj;
                
                pipe.setProductId(results.getInt("productId"));
                pipe.setDiameter(results.getInt("diameter"));
                pipe.setLength(results.getDouble("length"));
            }    
        }
        catch(Exception e)
        {
            System.out.println("error in building the ProductDescription object");
            System.out.println(e.getMessage());
        }
        return proObj;
   }

        @Override
        public int getMaxId()
        {
            int maxProductId = GetMax.getMaxId("Select max(productId) from Product");
            return maxProductId;
        }
}
